package cn.u313.repository;

import cn.u313.entry.JiLuEntry;
import cn.u313.entry.PresentChildrenEntry;
import cn.u313.entry.PresentEntry;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface PresentRepository extends JpaRepository<PresentEntry, Integer>, JpaSpecificationExecutor<PresentEntry> {
//    @Query(nativeQuery = true,value = "SELECT * FROM `t_yjilu` where id = '89152' ")
//    JiLuEntry byQQ(String s);
//    @Query(nativeQuery = true,value = "select count(1) from t_yjilu where to_days(time) = to_days(now()) and qq0 = :qq and type=:type ")
//    int countByqqAndType(String qq,String type);
//    @Query(nativeQuery = true,value = "select count(1) from t_yjilu where to_days(time) = to_days(now()) and qq0 = :qq and qq1 =:qq1")
//    int countQqToQq1(String qq,String qq1);

    @Query(nativeQuery = true,value = "SELECT * FROM `t_present_main` where has_Show = 1  ")
    List<PresentEntry> getAll();
    @Query(nativeQuery = true,value = "SELECT * FROM `t_present_main` where id = :id and  has_Show = 1  ")
    PresentEntry getbyId(String id);

    @Query(nativeQuery = true,value = "SELECT count(1) FROM `t_present_main` as a \n" +
            "\tINNER JOIN t_present_record b \n" +
            "\t\ton a.id=b.main_id \n" +
            "\t\tand b.main_id = :code \n" +
            "\t\tand strattime<b.date\n" +
            "\t\tand endtime > b.date\n" +
            "\t\tand b.qq=:qq ")
    int countByQqAndCode(String qq, String code);

    @Query(nativeQuery = true,value = "SELECT count(1) FROM `t_present_main` as a \n" +
            "\tINNER JOIN t_present_record b \n" +
            "\t\ton a.id=b.main_id \n" +
            "\t\tand b.main_id = :code \n" +
            "\t\tand  to_days(b.date) = to_days(now())\n" +
            "\t\tand b.qq=:qq ")
    int countByqqAndTypeAndThisToDay(String qq, String code);
    @Query(nativeQuery = true,value = "SELECT count(1) FROM `t_present_main` as a \n" +
            "\tINNER JOIN t_present_record b \n" +
            "\t\ton a.id=b.main_id \n" +
            "\t\tand b.main_id = :code \n" +
            "\t\tand strattime<b.date\n" +
            "\t\tand endtime > b.date\n")
    int countByCode(String code);

    @Query(nativeQuery = true,value = "SELECT count(1) FROM `t_present_main` as a \n" +
            "\tINNER JOIN t_present_record b \n" +
            "\t\ton a.id=b.main_id \n" +
            "\t\tand b.main_id = :code \n" +
            "\t\tand  to_days(b.date) = to_days(now())\n" )
    int countByTypeAndThisToDay(String code);
//    public

}